'use strict';

const conn = require('../../libs/Conn');
const _ = require('underscore');

module.exports = async function (params) {

    /*分页*/
    let pageASql = `LIMIT ${params.pageNum>1?((params.pageNum-1)*5):0},5;SELECT FOUND_ROWS() AS count;`;
    let pageBSql = `LIMIT ${params.pageNum>1?((params.pageNum-1)*5):0},5`;

    /*模糊查询*/
    let engineeringNoSql = ``;
    if(params.valueName&&params.valueName!==''){

            engineeringNoSql = `AND (engineering_no LIKE '%${params.valueName}%' OR engineering_name LIKE '%${params.valueName}%') `;

    }
    /*工程筛选*/
    let engineeringSql = ``;
    if(params.engineeringName_id){
        if(params.engineeringName_id!=='0') {
            engineeringSql = `AND engineering_id = '${params.engineeringName_id}' `;
        }
    }
    /*角色判断*/
    let userSql = ``;
    if(params.is_manager !==1){
        userSql = `AND engineering_id IN (               SELECT
                                                                engineering_id
                                                            FROM
                                                                engineering_file
                                                            WHERE
                                                                principal_id = '${params.user_id}'
                                                            AND delete_sign = 1
                                                            UNION
                                                                SELECT
                                                                    efa.engineering_id
                                                                FROM
                                                                    engineering_user_middle eum
                                                                INNER JOIN engineering_file efa ON efa.engineering_id = eum.engineering_id
                                                                AND efa.delete_sign = 1
                                                                WHERE
                                                                    eum.user_id = '${params.user_id}'
                                                                UNION
                                                                    SELECT
                                                                        epid.engineering_id
                                                                    FROM
                                                                        progress_information_user_middle pium
                                                                    INNER JOIN engineering_progress_information_data epid ON epid.information_id = pium.information_id AND epid.delete_sign = 1
                                                                WHERE  pium.user_id = '${params.user_id}'
                                                            UNION
                                                                    SELECT
                                                                        engineering_id
                                                                    FROM
                                                                        engineering_progress_information_data
                                                                WHERE delete_sign = 1 AND user_id = '${params.user_id}'
                                                            ) `;
    }
    let querySql = `SELECT 
                            SQL_CALC_FOUND_ROWS
                            engineering_id,
                            engineering_no,
                            IFNULL(engineering_name,'') AS engineering_name                          
                        FROM
                            engineering_file
                        WHERE
                            delete_sign = 1 ${userSql} ${engineeringNoSql} ${engineeringSql} ${pageASql}
                        
                        
                        SELECT
                            progress_type,
                            engineering_id,
                            information_id,
                            IFNULL(DATE_FORMAT(inspection_date, '%Y-%m-%d'),'') AS inspection_date,
                            IFNULL(DATE_FORMAT(
                                supervision_date,
                                '%Y-%m-%d'
                            ),'') AS supervision_date,
                            IFNULL(remarks,'') AS remarks
                            
                        FROM
                            engineering_progress_information_data
                        WHERE
                            engineering_id IN (
                                SELECT
                                    *
                                FROM
                                    (
                                        SELECT
                                            engineering_id
                                        FROM
                                            engineering_file
                                        WHERE
                                            delete_sign = 1 
                                        ${userSql} ${engineeringNoSql} ${engineeringSql} ${pageBSql}
                                    ) AS abc
                            )
                        AND delete_sign = 1
                        AND (
                            progress_type = 5
                            OR progress_type = 6
                        );
                        
                        SELECT
                            information_id,
                            IFNULL(user_name,'') AS user_name
                        FROM
                            progress_information_user_middle pium
                        LEFT JOIN user_file uf ON pium.user_id = uf.user_id
                        AND uf.delete_sign = 1
                        WHERE
                            pium.information_id IN (
                                SELECT
                                    information_id
                                FROM
                                    engineering_progress_information_data
                                WHERE
                                    engineering_id IN (
                                        SELECT
                                            *
                                        FROM
                                            (
                                                SELECT
                                                    engineering_id
                                                FROM
                                                    engineering_file
                                                WHERE
                                                    delete_sign = 1 
                                                ${userSql} ${engineeringNoSql} ${engineeringSql} ${pageBSql}                                              
                                            ) AS abc
                                    )
                                AND delete_sign = 1
                                AND (
                                    progress_type = 5
                                    OR progress_type = 6
                                )
                            );
                        
                        SELECT
                            information_id,
                            engineering_id,
                            IFNULL(DATE_FORMAT(
                                inspection_date,
                                '%Y-%m-%d'
                            ),'') AS inspection_date,
                            IFNULL(DATE_FORMAT(
                                completion_date,
                                '%Y-%m-%d'
                            ),'') AS completion_date,
                            IFNULL(DATE_FORMAT(
                                review_pass_date,
                                '%Y-%m-%d'
                            ),'') AS review_pass_date,
                            IFNULL(remarks,'') AS remarks
                        FROM
                            engineering_progress_completion
                        WHERE
                            engineering_id IN (
                                SELECT
                                    *
                                FROM
                                    (
                                        SELECT
                                            engineering_id
                                        FROM
                                            engineering_file
                                        WHERE
                                            delete_sign = 1 
                                        ${userSql} ${engineeringNoSql} ${engineeringSql} ${pageBSql}
                                    ) AS abc
                            );
                        
                        SELECT
                            information_id,
                            IFNULL(user_name,'') AS user_name
                        FROM
                            progress_information_user_middle pium
                        LEFT JOIN user_file uf ON pium.user_id = uf.user_id
                        AND uf.delete_sign = 1
                        WHERE
                            pium.information_id IN (
                                SELECT
                                    information_id
                                FROM
                                    engineering_progress_completion
                                WHERE
                                    engineering_id IN (
                                        SELECT
                                            *
                                        FROM
                                            (
                                                SELECT
                                                    engineering_id
                                                FROM
                                                    engineering_file
                                                WHERE
                                                    delete_sign = 1 
                                                ${userSql} ${engineeringNoSql} ${engineeringSql} ${pageBSql}
                                            ) AS abc
                                    )
                            );
                        
                        SELECT
                            engineering_id,
                            IFNULL(DATE_FORMAT(
                                inspection_date,
                                '%Y-%m-%d'
                            ) ,'')
                            AS inspection_date,
                            IFNULL(DATE_FORMAT(
                                filing_date,
                                '%Y-%m-%d'
                            ),'') AS filing_date,
                             IFNULL(remarks,'') AS remarks
                        FROM
                            engineering_progress_filing
                        WHERE
                            engineering_id IN (
                                SELECT
                                    *
                                FROM
                                    (
                                        SELECT
                                            engineering_id
                                        FROM
                                            engineering_file
                                        WHERE
                                            delete_sign = 1 
                                        ${userSql} ${engineeringNoSql} ${engineeringSql} ${pageBSql}
                                    ) AS abc
                            )
`;
    let rec = await conn(querySql);
    let engineeringData = rec[0];  //工程信息
    let progressData = rec[2];     //流程信息
    let progressUserData = rec[3]; //流程信息人员
    let completionData = rec[4];   //竣工验收
    let completionUserData = rec[5]; //竣工验收人员
    let filingData = rec[6]; //竣工备案


    progressData = _.groupBy(progressData,'progress_type');
    let basisData; //基础信息
    let mainBodyData; //主体信息
    for (let data in progressData){
        if(data === '5'){
            basisData = progressData[data];
        }else{
            mainBodyData = progressData[data];
        }
    }
    engineeringData = _.groupBy(engineeringData,'engineering_id');
    basisData = _.groupBy(basisData,'engineering_id');
    mainBodyData = _.groupBy(mainBodyData,'engineering_id');
    progressUserData = _.groupBy(progressUserData,'information_id');
    completionData = _.groupBy(completionData,'engineering_id');
    completionUserData = _.groupBy(completionUserData,'information_id');
    filingData = _.groupBy(filingData,'engineering_id');
    let lastDataList = [];
    for (let engineering in engineeringData) {
        /*序号和工程名称*/
        let engInformation = [{engineering_no:engineeringData[engineering][0].engineering_no,engineering_name:engineeringData[engineering][0].engineering_name}];
        /*基础信息数据*/
        let engBasisData = [];
        if(basisData[engineering]){
           engBasisData = basisData[engineering];
           _.each(engBasisData,function (data) {
               let information_id = data.information_id;
               let userList = progressUserData[information_id];
               let userName = '';
               _.each(userList,function (userData) {
                   userName += userData.user_name + ',';
               });
               if(userName.length>0){
                   userName=userName.slice(0,userName.length-1);
               }
               data.userName = userName;
           })
        }

       /*主体信息数据*/
        let engMainBodyData = [];
        if(mainBodyData[engineering]){
            engMainBodyData = mainBodyData[engineering];
            _.each(engMainBodyData,function (data) {
                let information_id = data.information_id;
                let userList = progressUserData[information_id];
                let userName = '';
                _.each(userList,function (userData) {
                    userName += userData.user_name + ',';
                });
                if(userName.length>0){
                    userName = userName.slice(0,userName.length-1);
                }
                data.userName = userName;
            })
        }

        /*竣工验收信息*/
        let engCompletionData = [];
        if(completionData[engineering]){
            engCompletionData = completionData[engineering];
            let information_id = engCompletionData[0].information_id;
            let userList = completionUserData[information_id];
            let userName = '';
            _.each(userList,function (userData) {
                userName += userData.user_name + ',';
            });
            if(userName.length>0){
                userName = userName.slice(0,userName.length-1);
            }
            engCompletionData[0].userName = userName;
        }

        /*竣工备案信息*/
        let engFilingData = [];
        if(filingData[engineering]){
            engFilingData = filingData[engineering]
        }

        let engData = [engInformation,engBasisData,engMainBodyData,engCompletionData,engFilingData];
        lastDataList.push(engData);
    }

    let pageNum = Math.ceil(rec[1][0].count/5);
    return {success: true, data:{dataTotal:rec[1][0].count,pageTotal:pageNum,pageNum:params.pageNum,dataList:lastDataList}};

};